/*
 * MySQL.cpp
 *
 *  Created on: 05.09.2013
 *      Author: radu
 */

#include "MySQL.h"

Statement *stmt;
ResultSet *res;
string fld_arr[20];
string tbl_name;
stringstream sql_str;
gint fld_cnt;
GType TypeArr[20];
GArray *products;
GtkWidget *entry1;
GtkWidget *entry2;
GtkTreeModel *items_model;
GtkTreeModel *products_model;



gboolean
  My_SQL::foreach_func (GtkTreeModel *model,
                GtkTreePath  *path,
                GtkTreeIter  *iter,
                gpointer      user_data)
  {
    gboolean _bool;
	gchar *_string;
    gint  _number;

    /* Note: here we use 'iter' and not '&iter', because we did not allocate
     *  the iter on the stack and are already getting the pointer to a tree iter */

    gtk_tree_model_get_iter (model, iter, path);

	sql_str.str("");
	sql_str << "INSERT INTO "<< tbl_name << "(";

    for(int i=0;i<=fld_cnt;i++)
    {
    	sql_str << fld_arr[i];
    	if (i!=fld_cnt) sql_str << ", ";
    }

    sql_str << ") VALUES (";

    for(int i=0;i<=fld_cnt;i++)
    {
		  if (TypeArr[i]==G_TYPE_STRING)
		  {
			  gtk_tree_model_get (model, iter, i, &_string, -1);
			  sql_str << "'" << _string << "'";
		  }
		  else if (TypeArr[i]==G_TYPE_INT)
		  {
			  gtk_tree_model_get (model, iter, i, &_number, -1);
			  sql_str << _number;
		  }
		  else if (TypeArr[i]==G_TYPE_BOOLEAN)
		  {
			  gtk_tree_model_get (model, iter, i, &_bool, -1);
			  sql_str << _bool;
		  }
		  if(i!=fld_cnt) sql_str << ",";
    }
	sql_str << ")";

	cout<<sql_str.str()<<endl;

	stmt->execute(sql_str.str());

    g_free(_string); /* gtk_tree_model_get made copies of       */
    /* the strings for us when retrieving them */

    return FALSE; /* do not stop walking the store, call us with next row */
  }


My_SQL::My_SQL() {
	// TODO Auto-generated constructor stub
	fld_cnt=0;
}

void My_SQL::fld_extract()
{
	for(int i=0;i<fld_cnt;i++)
		fld_arr[i].clear();

	fld_cnt=0;

	size_t pos, pos_sel, pos_frm, pos_whr;
	  std::string s1 = gtk_entry_get_text (GTK_ENTRY (entry1));

	  if (s1.find("SELECT")!= std::string::npos) pos_sel=s1.find("SELECT");
	  else if ((s1.find("select")!= std::string::npos)) pos_sel=s1.find("select");
	  else
	  {
	  	std::cout<<"Please insert a valid SQL statement!"<<std::endl;
	  	pos_sel=0;
	  }

	  if (s1.find("FROM")!= std::string::npos) pos_frm=s1.find("FROM");
	  else if ((s1.find("from")!= std::string::npos)) pos_frm=s1.find("from");
	  else
	  {
	  	std::cout<<"Please insert a valid SQL statement!"<<std::endl;
	  	pos_frm=0;
	  }

	  if (s1.find("WHERE")!= std::string::npos) pos_whr=s1.find("WHERE");
	  else if ((s1.find("where")!= std::string::npos)) pos_whr=s1.find("where");
	  else
	  {
	  	 pos_whr=s1.length();
	  }


	  std::string s = s1.substr(pos_sel, pos_frm);
	  tbl_name=s1.substr(pos_frm+5, pos_whr-s.length());
	  del_space(tbl_name);

	  s.erase(0,7);

	  del_space(s);

	  std::string delimiter = ",";

	  pos = 0;
	  std::string token;

	  while ((pos = s.find(delimiter)) != std::string::npos) {
	      token = s.substr(0, pos);
	  	  fld_arr[fld_cnt++] = token;
	      s.erase(0, pos + delimiter.length());
	  }
	  fld_arr[fld_cnt]=s;
}

void My_SQL::types_extract()
{
	  for(int i=0;i<fld_cnt;i++)
		  TypeArr[i]=0;

	  for(int i=0;i<=fld_cnt;i++)
	  {
		  string str="SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '";
		  str.append(tbl_name);
		  str.append("' AND COLUMN_NAME = '");
		  str.append(fld_arr[i]);
		  str.append("';");

			res = stmt->executeQuery(str);
			  if(res->next())
			  {
				if(strncmp("char",res->getString(1).c_str(),4)==0)
					TypeArr[i]=G_TYPE_STRING;
				else if (strncmp("int",res->getString(1).c_str(),3)==0)
					TypeArr[i]=G_TYPE_INT;
				else if (strncmp("tinyint",res->getString(1).c_str(),7)==0)
					TypeArr[i]=G_TYPE_BOOLEAN;
			  }
	  }
}

void
My_SQL::add_products ()
{
	stringstream msg;

	try {
			/*
			Run a query which returns exactly one result set like SELECT
			Stored procedures (CALL) may return more than one result set
			*/
			res = stmt->executeQuery(gtk_entry_get_text (GTK_ENTRY (entry2)));

			/* Fetching data */
			Prod prod;
			g_return_if_fail (products != NULL);
			while (res->next()) {
				prod.product=WChar_to_UTF8(GetWC(res->getString(1).c_str()));
				g_array_append_vals (products, &prod, 1);
			}
	} catch (sql::SQLException &e) {
		/*
		The MySQL Connector/C++ throws three different exceptions:

		- sql::MethodNotImplementedException (derived from sql::SQLException)
		- sql::InvalidArgumentException (derived from sql::SQLException)
		- sql::SQLException (derived from std::runtime_error)
		*/
		cout << "\n# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		/* Use what() (derived from std::runtime_error) to fetch the error message */
		cout << "\n# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
		cout << "not ok 1 - examples/connect.php" << endl;

	} catch (std::runtime_error &e) {

		cout << "\n# ERR: runtime_error in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "\n# ERR: " << e.what() << endl;
		cout << "not ok 1 - examples/connect.php" << endl;

	}
}

GtkTreeModel *
My_SQL::create_items_model (void)
{
  gint i = 0;
  GtkListStore *model;
  GtkTreeIter iter;

  fld_extract();

  types_extract();

  /* create list store */

  model = gtk_list_store_newv (fld_cnt+1, TypeArr);

  /* add items */
  res = stmt->executeQuery(gtk_entry_get_text (GTK_ENTRY (entry1)));

  //counter=res->rowsCount();

  /* Fetching data */
  while (res->next())
  {
      gtk_list_store_append (model, &iter);
      for(i=0;i<=fld_cnt;i++)
      {
		  if (TypeArr[i]==G_TYPE_STRING) gtk_list_store_set (model, &iter, i, WChar_to_UTF8(GetWC(res->getString(i+1).c_str())), -1);
		  else if (TypeArr[i]==G_TYPE_INT) gtk_list_store_set (model, &iter, i, res->getInt(i+1), -1);
		  else if (TypeArr[i]==G_TYPE_BOOLEAN) gtk_list_store_set (model, &iter, i, res->getBoolean(i+1), -1);
      }
   }
  return GTK_TREE_MODEL (model);
}

GtkTreeModel *
My_SQL::create_products_model (void)
{
  gint i = 0;
  GtkListStore *model;
  GtkTreeIter iter;

  /* create array */
  products = g_array_sized_new (FALSE, FALSE, sizeof (Prod), 1);

  add_products ();

  /* create list store */
  model = gtk_list_store_new (NUM_PRODUCT_COLUMNS, G_TYPE_STRING, G_TYPE_BOOLEAN);

  /* add products */
  for (i = 0; i < products->len; i++)
    {
      gtk_list_store_append (model, &iter);

      gtk_list_store_set (model, &iter,
    		  	  	  COLUMN_PRODUCT_TEXT,
    		  	  	  g_array_index (products, Prod, i).product,
    		  	  	  -1);
    }

  return GTK_TREE_MODEL (model);

#undef N_NUMBERS
}
void My_SQL::save_items(GtkWidget *button, gpointer data)
{
	  GtkTreeModel *model = items_model;

	  fld_extract();

	  types_extract();

	  string sql="DELETE FROM ";
	  sql.append(tbl_name);

	  stmt->execute(sql);

	  gtk_tree_model_foreach(GTK_TREE_MODEL(model), foreach_func, NULL);
	  cout << "\n#\t Records saved" << endl;
}

My_SQL::~My_SQL() {
	// TODO Auto-generated destructor stub
}

